require 'rubygems'
require 'sqlite3'
require 'singleton'
require 'Pelicula.rb'
require 'Contexto.rb'

class DbAccess
  include Singleton
  
  def initialize
    @db = SQLite3::Database.new(Contexto.instance.db)
  end
  
  def dvds
    dvds = []
    @db.execute("SELECT DISTINCT dvd FROM mdb_peliculas_x_dvd").each do |dvd|
	  dvds << dvd[0]
	end
	return dvds
  end

  def addPelicula peli
    @db.transaction do
      @db.execute("INSERT INTO MDB_PELICULAS VALUES ('#{peli.titulo}', #{peli.anno.to_i}, '#{peli.director}', '#{peli.sinopsis}')")
	end
  end
  
  def addPelicula_x_Dvd (peli, dvd)
    @db.transaction do
      @db.execute("INSERT INTO MDB_PELICULAS_X_DVD VALUES('#{dvd}', '#{peli.titulo}', #{peli.anno.to_i}, 'N')")
	end
  end
  
  def peliculas pelicula
    dvds = {}
	sql = "SELECT titulo, anno, director, sinopsis FROM mdb_peliculas WHERE "
	parametros = 0
	if !pelicula.titulo.nil?
	  sql << "titulo = '#{pelicula.titulo}'"
	  parametros += 1
	end
	if pelicula.anno != 0
	  if parametros == 0
	    sql << " AND "
	  end
	  sql << "anno = #{pelicula.anno}"
	end
	if !pelicula.director.nil?
	  if parametros == 0
	    sql << " AND "
	  end
	  sql << "director = '#{pelicula.director}'"
	  parametros+=1
	end
	stmt = @db.prepare("SELECT dvd FROM mdb_peliculas_x_dvd WHERE titulo = ? AND anno = ?")
    @db.execute(sql).each do |peli|
	  dvd = stmt.get_first_row peli[0], peli[1]
	  newPeli = Pelicula.new
	  newPeli.titulo = peli[0]
	  newPeli.anno = peli[1]
	  newPeli.director = peli[2]
	  newPeli.sinopsis = peli[3]

	  donde = "Disco"
	  if !(dvd[0].nil? or dvd[0] == "")
	    donde = dvd[0]
	  end
      peliculas[donde] << newPeli
	end
	return peliculas
  end
  
  def peliculasXdvd pelicula, dvd
    dvds = {}
    sql = "SELECT a.titulo, a.anno, a.director, a.sinopsis, b.dvd FROM mdb_peliculas a, mdb_peliculas_x_dvd b WHERE a.titulo = b.titulo AND a.anno = b.anno"
    if !(dvd.nil? or dvd == "")
      sql << " AND b.dvd = '#{dvd}'"
    end
    if !(pelicula.titulo.nil? or pelicula.titulo == "")
      sql << " AND a.titulo like '%#{pelicula.titulo}%'"
    end
    if !(pelicula.anno.nil? or pelicula.anno == 0)
      sql << " AND a.anno = #{pelicula.anno}"
    end
    if !(pelicula.director.nil? or pelicula.director == "")
      sql << " AND a.director like '%#{pelicula.director}%'"
    end
	
	@db.execute(sql).each do |peli_x_dvd|
	  peli = Pelicula.new
	  peli.titulo = peli_x_dvd[0]
	  peli.anno = peli_x_dvd[1].to_i
	  peli.director = peli_x_dvd[2]
	  peli.sinopsis = peli_x_dvd[3]
	  
	  if dvds[peli_x_dvd[4]].nil?
	    dvds[peli_x_dvd[4]] = []
	  end
	  dvds[peli_x_dvd[4]] << peli
	end

	return dvds
  end

  def pelicula pelicula
    peliculas = []
	sql = "SELECT titulo, anno, director, sinopsis FROM mdb_peliculas WHERE "
	parametros = 0
	if pelicula.nil?
	  sql << "1 = 1"
	else
	  if !(pelicula.titulo.nil? or pelicula.titulo == "")
	    sql << "titulo = '#{pelicula.titulo}'"
	    parametros += 1
	  end
	  if !(pelicula.anno.nil? or pelicula.anno == 0)
	    if parametros == 0
	      sql << " AND "
	    end
	    sql << "anno = #{pelicula.anno}"
	  end
	  if !(pelicula.director.nil? or pelicula.director == "")
	    if parametros == 0
	      sql << " AND "
	    end
	    sql << "director = '#{pelicula.director}'"
	    parametros+=1
	  end
	end
	@db.execute(sql).each do |peli|
	  newPeli = Pelicula.new
	  newPeli.titulo = peli[0]
	  newPeli.anno = peli[1]
	  newPeli.director = peli[2]
	  newPeli.sinopsis = peli[3]
	  
	  peliculas << newPeli
	end
	return peliculas
  end
  
end